package com.cx.bank.util;

import com.cx.bank.pojo.Log;
import com.cx.bank.pojo.UserBean;

import java.io.InputStream;
import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;

public class JdbcUtil {
    public void testConnection1() {
        try {
            //1.??java.sql.Driver????????????
            Driver driver = null;
            driver = new com.mysql.cj.jdbc.Driver();

            //2.??url??????????????????
            String url = "jdbc:mysql://localhost:3306/properties";

            //3.??Properties???????????????????
            Properties info = new Properties();
            info.setProperty("user", "root");
            info.setProperty("password", "123456");

            //4.????driver??connect()?????????
            Connection conn = driver.connect(url, info);
            System.out.println(conn);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public  Connection getConnection()  {
        //1.???????????
        try {
            InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);
            //2.??????????
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");
            //3.????????
            Class.forName(driverClass);
            //4.???????
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    //???????
    public Map<Integer,UserBean> queryAll() {

        try {
            JdbcUtil jdbcUtil = new JdbcUtil();
            Connection connection = jdbcUtil.getConnection();
            String sqlString = " select * from properties.t_user ";
            PreparedStatement statement = connection.prepareStatement(sqlString);
            ResultSet rs =statement.executeQuery();
            Map<Integer, UserBean> map = new TreeMap<>();
            int count=0;
            while (rs.next()) {
                map.put(count, new UserBean(rs.getString(2), rs.getString(3), rs.getDouble(4), rs.getInt(5), rs.getInt(6)));count++;
            }
            return map;
        } catch (Exception e) {
            e.printStackTrace();
        }return null;
    }

    public Map<Integer, Log> queryLogById(int userId){
        try {
            JdbcUtil jdbcUtil = new JdbcUtil();
            Connection connection = jdbcUtil.getConnection();
            String sqlString = " select * from properties.t_log where userid=?";
            PreparedStatement statement = connection.prepareStatement(sqlString);
            statement.setInt(1,userId);
            ResultSet rs =statement.executeQuery();
            Map<Integer,Log> map =new TreeMap<>();
            int count=0;
            while (rs.next()) {
                map.put(count,new Log(rs.getInt(1),rs.getString(2),rs.getDouble(3), rs.getInt(4)));
                count++;
            }
            return map;
        } catch (Exception e) {
            e.printStackTrace();
        }return null;


    }
    public static void main(String[] args) {
        JdbcUtil jdbcUtil =new JdbcUtil();
        Map<Integer,UserBean> map =jdbcUtil.queryAll();
        System.out.println("==========");
        for (int i=0;i<map.size();i++) {
            System.out.println(map.get(i).getUserName());
        }
    }
}
